Re: [SQL] Oddities with NULL and GROUP BY - Mailing list pgsql-sql

At 18:28 +0300 on 17/05/1999, José Soares wrote:


> The Pratical SQL Handbook at page 171 says:
> Since nulls represent "the great unknown", there is no way to know
> whether one null is equal to any other null. Each unknown value
> may or may not be different from another.
> However, if the grouping column contains more than one null,
> all of them are put into a single group.
>
> Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.

This is something I have complained about time and again. It is time
something is changed about it, otherwise Postgres will NEVER be a
standard-compliant RDBMS.

The SQL92 text says:
    A null value is an implementation-dependent special value that    is distinct from all non-null values of the
associateddata type.    There is effectively only one null value and that value is a member    of every SQL data type.
Thereis no <literal> for a null value,    although the keyword NULL is used in some places to indicate that a    null
valueis desired.
 

Thus, by rights, NULL=NULL should be true, because there is only one null
value.

About the <group by clause>, the text says:
   1) The result of the <group by clause> is a partitioning of T into      a set of groups. The set is the minimum
numberof groups such      that, for each grouping column of each group of more than one      row, no two values of that
groupingcolumn are distinct.
 

And the treatment of nulls is implied from the definition of distinctness:
   h) distinct: Two values are said to be not distinct if either:      both are the null value, or they compare equal
accordingto      Subclause 8.2, "<comparison predicate>". Otherwise they are      distinct. Two rows (or partial rows)
aredistinct if at least      one of their pairs of respective values is distinct. Otherwise      they are not distinct.
Theresult of evaluating whether or not      two values or two rows are distinct is never unknown.
 

About uniqueness, it says:
   A unique constraint is satisfied if and only if no two rows in   a table have the same non-null values in the unique
columns.In   addition, if the unique constraint was defined with PRIMARY KEY,   then it requires that none of the
valuesin the specified column or   columns be the null value.
 

One should note, however, that when the actual comparison operator "=" is
used, the standard says that if one of the operands is null, the result of
the comparison is unknown. One should make a distinction between making
comparisons within group by, uniqueness, and other database-logic
operations, and between making the actual comparison (though in my opinion,
this should not be so. Comparing a null value to something should be always
false unless the other something is also null. But that's my opinion and
not the standard's).

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma